Using Database Principles to Optimize SAS® Format Construction from Tabular Data

While formats are more efficient than standard lookup tables for retrieving target values, guidelines still need to be established for building the best possible format from a control data set. More specifically, the guidelines need to link the format as a function where label=f(range) to the format's underlying table as a two-dimensional relation where entities and attributes are identified by primary keys. Fortunately, database cardinality and normalization concepts can be expanded to embrace these seemingly disparate constructs. An identity is established between a mathematic "function" and database theory's "first normal form" required for format construction. The paper then shows what happens when formats are built that either conform to or fail to meet requirements for second and third normal forms. Terms are defined and concepts are explained by diagram and example. An additional section deals with building a cluster of formats that reflect the structure of FDA data used for generating the segmented National Drug Code. Insight into database concepts should help users optimize format construction from two-dimensional tables. The twodimensional model is also extended to cover the generation of multiple formats for data that gradually change over time. Since most medical codes are updated annually, this paper is particularly relevant for those who work in the pharmaceutical industry. INTRODUCTION With the addition of significant functionality, PROC FORMAT has become a major component in the SAS programming language. A major improvement dates back to the early 1990's when SAS made it possible to convert a SAS data set to a format. Initially the data sets contained two columns: one for the format range and a second for a corresponding label. Subsequently, SAS users extended the source data to encompass two-dimensional tables of related attributes containing both row and column identifiers. With an underlying tabular structure, database management techniques have become increasingly relevant in format construction and management. Two techniques are cardinality and normalization. Cardinality provides insight about how entities with identifying keys relate to each other, and normalization describes a series of steps or normal forms that result in tables with a simplified, robust structure. Since going from table to format is a minor step, this paper shows how normalization techniques translate to formats that are efficient as well as easy to process and maintain. Key database, mathematical and SAS terms are defined. The definitions should enable those unfamiliar with database principles or mathematical notation to understand the concepts that are being discussed. However, a basic familiarity with the FORMAT procedure is assumed. This means no special attempt will be made to explain syntax or to provide instructions for building a format from a control data set. For an excellent introductory guide to the FORMAT procedure, see The Power of PROC FORMAT by Jonas Bilenas. Published in 2005 by SAS Press, it is part of Art Carpenter's SAS® Software Series. A SUGI paper, SAS® Software Formats: Going Beneath the Surface by Roger Staum, provides the mathematical foundation upon which this paper is based. FIRST NORMAL FORM (1NF): REQUIRED FOR CONSTRUCTING A FORMAT RELEVANT TERMINOLOGY FOR FIRST NORMAL FORM